# coding=gb2312
import importlib
import os
import sys

from openpyxl.reader.excel import load_workbook

import xlsx
from finder import fs

importlib.reload(sys)
# sys.setdefaultencoding('utf-8')


def make_script(book_name):
    """"""
    wb = load_workbook(filename="xlsx/%s" % book_name)
    names = []
    for shtName in wb.get_sheet_names():
        names.append(shtName)
    names.reverse()
    lines = []
    for shtName in names:
        lines.append("drop table if exists %s;" % shtName)
    lines.append("\n")
    names.reverse()
    for shtName in names:
        lines.append("source ddl/%s/%s.sql;" % (book_name, shtName))
    fs.write("ddl/%s.ddl.sql" % book_name, lines)
    print("ddl/%s.ddl.sql." % book_name)


def process_ddl(book_name):
    """"""
    if not os.path.exists("ddl/%s" % book_name):
        os.makedirs("ddl/%s" % book_name)
    wb = load_workbook(filename="xlsx/%s" %book_name)
    for shtName in wb.get_sheet_names():
        records = build_ddl(wb, shtName)
        fs.write("ddl/%s/%s.sql" % (book_name, shtName), records)
        print("%s.sql" % shtName)


def build_ddl(wb, sheet_name):
    """"""
    ws = wb.get_sheet_by_name(sheet_name)
    rtn = []
    mr = ws.max_row + 1
    last_row = xlsx.get_last_non_merged_row(ws)
    for r in range(2, mr):
        field_name = str(ws.cell(row=r, column=1).value)
        field_type = ws.cell(row=r, column=2).value
        tmp_misc = str(ws.cell(row=r, column=3).value)
        if tmp_misc == "None":
            field_misc = ""
        else:
            field_misc = tmp_misc

        tmp_default = str(ws.cell(row=r, column=4).value)
        if tmp_default == "None":
            field_default = ""
        elif tmp_default.upper() in ('NULL', '0'):
            field_default = "DEFAULT %s" % tmp_default
        elif tmp_default.upper().startswith('CURRENT_TIMESTAMP'):
            field_default = "DEFAULT %s" % tmp_default
        else:
            field_default = "DEFAULT '%s'" % tmp_default

        tmp_comment = ws.cell(row=r, column=5).value
        if tmp_comment == "None":
            field_comment = "COMMENT ''"
        else:
            field_comment = "COMMENT '%s'" % tmp_comment

        if xlsx.is_merged(ws, r):
            line = "%s" % field_name
        else:
            if r == last_row:
                line = "    `%s` %s %s %s %s" % (field_name, field_type, field_misc, field_default, field_comment)
            else:
                line = "    `%s` %s %s %s %s," % (field_name, field_type, field_misc, field_default, field_comment)
        rtn.append(line)
    return rtn
